from flask import Blueprint, render_template, request, redirect, url_for, session, jsonify
import json
from flask import request
import pymysql
from config import Config
# from flask_sqlalchemy import SQLAlchemy
# from sqlalchemy import *
# from model.china_job import China_job
# db = SQLAlchemy()
# 1
pc = Blueprint('province', __name__)

@pc.route('/city', methods=['POST'])
def get_city():
    province = None
    city = None
    if request.method == 'POST':
        data = json.loads(request.data.decode())
        city = data['city']
        province = data['province'][:-1]
    connect = pymysql.connect(host=Config.MYSQL_HOST, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASSWD,
                              port=Config.MYSQL_PORT, database=Config.MYSQL_DATABASE)
    cursor = connect.cursor()
    if city == 'province':

        # 柱状图 所有城市的平均薪资
        bar_axis = []
        bar_data = []
        bar_attr = {'name': '平均薪资', 'unit': '元'}
        bar_title = 'Top10城市平均薪资情况'
        # bar_rows = db.session.query(China_job.city,
        #                             (func.avg(func.SUBSTRING_INDEX(China_job.salary, "-", 1)
        #                                       + func.SUBSTRING_INDEX(China_job.salary, "-", -1)) / 2).label('s')). \
        #                filter(China_job.province == province).group_by(China_job.city).order_by(desc("s")).all()[:10]
        cursor.execute('select city, avg((substring_index(salary, "-", 1)+substring_index(salary, "-", -1))/2) as s'
                       ' from china_job where province = %s group by city order by s desc limit 10', (province,))
        bar_rows = cursor.fetchall()
        for row in bar_rows:
            bar_axis.append(row[0])
            bar_data.append(round(row[1], 2))

        # 饼图 计算收入前十的类型
        # pie_rows = db.session.query(China_job.position, func.count(China_job.position).label('s')). \
        #                group_by(China_job.position).filter(China_job.province == province).all()[:10]

        cursor.execute('select position, count(*) as c from china_job'
                       ' where province = %s group by position order by c desc limit 15', (province,))
        pie_rows = cursor.fetchall()
        pie_data = []
        pie_names = []
        pie_name = '岗位Top'
        pie_number = 0
        for row in pie_rows:
            pie_number += row[1]
            pie_names.append(row[0])
            pie_data.append(row[1])

        # 雷达图 计算各个学历的占比
        # radar_rows = db.session.query(China_job.education, func.count(China_job.education).label('c'),
        #                               China_job.city). \
        #     filter(China_job.province == province).group_by(China_job.city, China_job.education). \
        #     order_by('c').all()
        cursor.execute('select education, count(*) as c ,city from china_job '
                       'where province = %s group by city, education order by c', (province,))
        radar_rows = cursor.fetchall()
        radar_title = "学历与城市的关系"
        radar_indi = [
            {'name': '不限'},
            {'name': '本科'},
            {'name': '大专'},
            {'name': '中专'},
            {'name': '技校'},
            {'name': '高中'},
        ]
        radar_temp = {}
        for row in radar_rows:
            if row[2] in radar_temp.keys():
                radar_temp[row[2]].append(row[1])
            else:
                radar_temp[row[2]] = [row[1]]
        radar_legend = []
        radar_series = []
        for city in radar_temp:
            radar_legend.append(city)
            radar_series.append({
                'value': radar_temp[city],
                'name': city,
                'itemStyle': {
                    'normal': {
                        'lineStyle': {
                            'color': '#4BFFFC',
                        },
                        'shadowColor': '#4BFFFC',
                        'shadowBlur': 10,
                    },
                },
                'areaStyle': {
                    'normal': {
                        'color': {
                            'type': 'linear',
                            'x': 0,
                            'y': 0,
                            'x2': 1,
                            'y2': 1,
                            'colorStops': [{
                                'offset': 0,
                                'color': '#4BFFFC'
                            }, {
                                'offset': 0.5,
                                'color': 'rgba(0,0,0,0)'
                            }, {
                                'offset': 1,
                                'color': '#4BFFFC'
                            }],
                            'globalCoord': 'false'
                        },
                        'opacity': 1
                    }
                }
            })

        # 漏斗图
        # funnel_data1 岗位个数
        funnel_data1 = []
        # funnel_data2 占比
        funnel_data2 = []
        funnel_title = '各个城市岗位种类数占比'
        funnel_name = '岗位种类数'
        # funnel_rows = db.session.query(China_job.city, func.count(China_job.position)). \
        #     filter(China_job.province == province).group_by(China_job.city).all()
        cursor.execute('select city, count(distinct(position)) from china_job where province = %s group by city',
                       (province,))
        funnel_rows = cursor.fetchall()
        funnel_sum = 0
        for row in funnel_rows:
            funnel_data1.append({'name': row[0], 'value': row[1]})
            funnel_sum += row[1]
        for row in funnel_rows:
            funnel_data2.append({'name': row[0], 'value': int(row[1] / funnel_sum * 100)})
        # 旋转图
        # xz_rows = db.session.query(func.count(China_job.position)). \
        #     filter(China_job.province == province).group_by(China_job.position).all()
        cursor.execute('select distinct(position) from china_job where province = %s ', (province,))
        xz_rows = cursor.fetchall()
        number = len(xz_rows)
        number_format = "种岗位"
        return jsonify({'bar_axis': bar_axis, 'bar_data': bar_data, 'bar_title': bar_title, 'bar_attr': bar_attr,
                        'pie_data': pie_data, 'pie_name': pie_name, 'pie_names': pie_names, 'pie_number': pie_number,
                        'radar_title': radar_title, 'radar_indi': radar_indi, 'radar_legend': radar_legend,
                        'radar_series': radar_series, 'funnel_name': funnel_name, 'funnel_data1': funnel_data1,
                        'funnel_data2': funnel_data2, 'funnel_title': funnel_title,
                        'number': number, 'number_format': number_format})



    # 市级
    else:
        # 柱状图 Top10工作类型
        bar_axis = []
        bar_data = []
        bar_attr = {'name': '薪资', 'unit': '元'}
        bar_title = city+'市薪资Top10工作类型'
        # bar_rows = db.session.query(China_job.position,
        #                             (func.avg(func.SUBSTRING_INDEX(China_job.salary, "-", 1)
        #                                       + func.SUBSTRING_INDEX(China_job.salary, "-", -1)) / 2).label('s')). \
        #                filter(China_job.city == city).group_by(China_job.position).order_by(desc("s")).all()[
        #            :10]
        cursor.execute('select position, avg((substring_index(salary, "-", 1)+substring_index(salary, "-", -1))/2) as s'
                       ' from china_job where city = %s group by position order by s desc limit 10', (city,))
        bar_rows = cursor.fetchall()
        for row in bar_rows:
            bar_axis.append(row[0].split("/")[0])
            bar_data.append(round(row[1], 2))

        # 饼图 计算收入前十五的类型
        # pie_rows = db.session.query(China_job.position, func.count(China_job.position).label('s')). \
        #                filter(China_job.city == city).group_by(China_job.position).all()[:15]
        cursor.execute('select position, count(*) as c from china_job'
                       ' where city = %s group by position order by c desc limit 15', (city,))
        pie_rows = cursor.fetchall()
        pie_data = []
        pie_names = []
        pie_name = '岗位Top'
        pie_number = 0
        for row in pie_rows:
            pie_number += row[1]
            pie_names.append(row[0])
            pie_data.append(row[1])
        # 雷达图 计算各个学历的占比
        # radar_rows = db.session.query(China_job.education,
        #                               func.count(China_job.education).label('c'), China_job.city).\
        #     filter(China_job.city == city).group_by(China_job.city, China_job.education).\
        #     order_by('c').all()
        cursor.execute('select education, count(education) as c, city from china_job'
                       ' where city = %s group by city, education order by c', (city,))
        radar_rows = cursor.fetchall()
        radar_title = city+"市岗位学历需求"
        radar_indi = [
            {'name': '不限'},
            {'name': '本科'},
            {'name': '大专'},
            {'name': '中专'},
            {'name': '技校'},
            {'name': '高中'},
        ]
        radar_temp = {}
        for row in radar_rows:
            if row[2] in radar_temp.keys():
                radar_temp[row[2]].append(row[1])
            else:
                radar_temp[row[2]] = [row[1]]
        radar_legend = []
        radar_series = []
        for city in radar_temp:
            radar_legend.append(city)
            radar_series.append({
                'value': radar_temp[city],
                'name': city,
                'itemStyle': {
                    'normal': {
                        'lineStyle': {
                            'color': '#4BFFFC',
                        },
                        'shadowColor': '#4BFFFC',
                        'shadowBlur': 10,
                    },
                },
                'areaStyle': {
                    'normal': {
                        'color': {
                            'type': 'linear',
                            'x': 0,
                            'y': 0,
                            'x2': 1,
                            'y2': 1,
                            'colorStops': [{
                                'offset': 0,
                                'color': '#4BFFFC'
                            }, {
                                'offset': 0.5,
                                'color': 'rgba(0,0,0,0)'
                            }, {
                                'offset': 1,
                                'color': '#4BFFFC'
                            }],
                            'globalCoord': 'false'
                        },
                        'opacity': 1
                    }
                }
            })

        # 漏斗图
        # funnel_data1 岗位个数
        funnel_data1 = []
        # funnel_data2 占比
        funnel_data2 = []
        funnel_name = '经验'
        funnel_title = city+"市的工作经验需求"
        # funnel_rows = db.session.query(China_job.experience, func.count(China_job.position)). \
        #     filter(China_job.city == city).group_by(China_job.experience).all()
        cursor.execute('select experience, count(distinct(position)) from china_job'
                       ' where city  = %s group by experience', (city,))
        funnel_rows = cursor.fetchall()
        funnel_sum = 0
        for row in funnel_rows:
            funnel_data1.append({'name': row[0], 'value': row[1]})
            funnel_sum += row[1]
        for row in funnel_rows:
            funnel_data2.append({'name': row[0], 'value': int(row[1] / funnel_sum * 100)})
        # 旋转图
        # xz_rows = db.session.query(func.count(China_job.position)). \
        #     filter(China_job.city == city).group_by(China_job.position).all()
        cursor.execute('select distinct(position) from china_job where city = %s', (city,))
        xz_rows = cursor.fetchall()
        number = len(xz_rows)
        number_format = "种岗位"
        return jsonify({'bar_axis': bar_axis, 'bar_data': bar_data, 'bar_title': bar_title, 'bar_attr': bar_attr,
                        'pie_data': pie_data, 'pie_name': pie_name, 'pie_names': pie_names,'pie_number': pie_number,
                        'radar_title': radar_title, 'radar_indi': radar_indi, 'radar_legend': radar_legend,
                        'radar_series': radar_series, 'funnel_name': funnel_name, 'funnel_data1': funnel_data1,
                        'funnel_data2': funnel_data2, 'funnel_title': funnel_title,
                        'number': number, 'number_format': number_format})
